import pymysql


class DBTools(object):
    @staticmethod
    def __create_connect():
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()

        except Exception as err:
            print("异常信息提示:", err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_all(sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            cursor.rownumber = 0
            res = cursor.fetchall()

        except Exception as err:
            print("异常信息提示:", err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_many(size, sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(size)

        except Exception as err:
            print("异常信息提示:", err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def db_iud(sql):
        conn = None
        cursor = None
        try:
            conn = DBTools.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            print("事务提交成功")
        except Exception as err:
            conn.rollback()
            print("回滚事务")
        finally:
            cursor.close()
            conn.close()


if __name__ == '__main__':
    res = DBTools.query_one("select * from t_book where id = 1111111;")
    print("res", res)
    res1 = DBTools.query_all("select * from t_book;")
    print("res1", res1)
    res2 = DBTools.query_many(3, "select * from t_book;")
    print("res2", res2)
    res3 = DBTools.db_iud("delete from t_book where id = 15")


